const main = {
  add: `INSERT INTO Jobs
    (userId, jName, jAvatar, startTime, endTime, minDuration, region, jAddress, position, areas, rights, jType, salary, content) VALUES
    (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
  update: `UPDATE Jobs SET
    jName = ?,
    jAvatar = ?,
    startTime = ?,
    endTime = ?,
    minDuration = ?,
    region = ?,
    jAddress = ?,
    position = ?,
    areas = ?,
    rights = ?,
    jStatus = ?,
    salary = ?,
    content = ?
    WHERE id = ?`,
  select: `SELECT j.id. j.jName, j.jAvatar, j.region, j.jType, j.salary, c.cName, c.authorizeTime FROM Jobs j
    LEFT JOIN CompanyAuthorize c ON c.id = j.userId
    WHERE (? IS NULL OR j.jName LIKE CONCAT('%', ?, '%'))
    AND (? IS NULL OR c.cName LIKE CONCAT('%', ?, '%'))
    AND (? IS NULL OR j.region LIKE CONCAT('%', ?, '%'))
    AND (? IS NULL OR j.jAddress LIKE CONCAT('%', ?, '%'))
    AND (? IS NULL OR j.startTime >= ?)
    AND (? IS NULL OR j.userId = ?)
    AND (? IS NULL OR j.jStatus = ?)
    ORDER BY startTime DESC
    LIMIT ?, ?`,
  count: `SELECT COUNT(*) FROM Jobs j
    LEFT JOIN CompanyAuthorize c ON c.id = j.userId
    WHERE (? IS NULL OR j.jName LIKE CONCAT('%', ?, '%'))
    AND (? IS NULL OR c.cName LIKE CONCAT('%', ?, '%'))
    AND (? IS NULL OR j.region LIKE CONCAT('%', ?, '%'))
    AND (? IS NULL OR j.jAddress LIKE CONCAT('%', ?, '%'))
    AND (? IS NULL OR j.startTime >= ?)
    AND (? IS NULL OR j.userId = ?)
    AND (? IS NULL OR j.jStatus = ?)`,
  detail: `SELECT j.*, c.* FROM Jobs j
    LEFT JOIN CompanyAuthorize c ON c.id = j.userId
    WHERE j.id = ?`
};

module.exports = main;